const express = require('express');
const mysql = require('mysql');
const bodyParser = require('body-parser');
const cors = require('cors');
const http = require('http');
const socketIo = require('socket.io');
const aiRoutes = require('../../router/ai.js'); // 引入 ai 路由文件


// 创建应用实例
const app = express();

// 定义端口
const port = 3000;   // HTTP API 服务端口
const httpPort = 3001;  // HTTP 路由和API服务端口
const wsPort = 3002;    // WebSocket 服务端口

// 创建MySQL连接
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '123456',
  database: 'elderly',
});

// 连接到MySQL
db.connect((err) => {
  if (err) {
    console.error('MySQL 连接失败:', err);
    process.exit(1);  // 如果连接失败，退出程序
  }
  console.log('MySQL Connected...');
});

// 设置 CORS 和 bodyParser 中间件
app.use(cors());
app.use(bodyParser.json());


// 使用JSON中间件
app.use(express.json());

// 配置 CORS，允许前端请求
app.use(cors({
  origin: 'http://localhost:7000',  // 确保允许来自前端的请求
  methods: 'GET,POST',
  allowedHeaders: 'Content-Type,Authorization',
}));


app.use('/api/ai', aiRoutes);  // 使用 ai 路由

// 创建 HTTP 服务器
const httpServer = http.createServer(app);

// 启动 HTTP 服务器（API 路由）
httpServer.listen(httpPort, () => {
  console.log(`API Server is running on port ${httpPort}`);
});

// 创建 WebSocket 服务器
const wsServer = http.createServer();  // 单独的 WebSocket 服务器

const io = socketIo(wsServer, {
  cors: {
    origin: 'http://localhost:7000', // 允许来自前端的 WebSocket 请求
    methods: ['GET', 'POST'],
    allowedHeaders: ['Content-Type', 'Authorization'],
  }
});

// WebSocket 连接处理
io.on('connection', (socket) => {
  console.log('A user connected');

  // 处理 WebSocket 消息
  socket.on('message', (data) => {
    console.log('Message from client: ', data);
    socket.emit('response', `You sent: ${data}`);
  });

  socket.on('disconnect', () => {
    console.log('User disconnected');
  });
});

// 启动 WebSocket 服务器
wsServer.listen(wsPort, () => {
  console.log(`WebSocket Server is running on port ${wsPort}`);
});










// 获取所有老人信息
app.get('/api/elderly', (req, res) => {
  const sql = 'SELECT * FROM elderly_management';
  db.query(sql, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});



// 获取所有老人信息
app.get('/api/elderly', (req, res) => {
  const sql = 'SELECT * FROM elderly_management';
  db.query(sql, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

// 添加新老人
app.post('/api/elderly', (req, res) => {
  const { name, age, room, status, health, contact, admissionDate, medicalRecords, activityParticipation, dietaryPreferences } = req.body;
  
  // 格式化 admissionDate
  const formattedAdmissionDate = new Date(admissionDate).toISOString().split('T')[0];

  const sql = `INSERT INTO elderly_management (name, age, room, status, health, contact, admissionDate, medicalRecords, activityParticipation, dietaryPreferences) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
  db.query(sql, [name, age, room, status, health, contact, formattedAdmissionDate, medicalRecords, activityParticipation, dietaryPreferences], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Elderly added successfully', id: result.insertId });
  });
});

// 更新老人信息
app.put('/api/elderly/:id', (req, res) => {
  const { name, age, room, status, health, contact, admissionDate, medicalRecords, activityParticipation, dietaryPreferences } = req.body;
  
  // 格式化 admissionDate
  const formattedAdmissionDate = new Date(admissionDate).toISOString().split('T')[0];

  const sql = `UPDATE elderly_management SET name = ?, age = ?, room = ?, status = ?, health = ?, contact = ?, admissionDate = ?, medicalRecords = ?, activityParticipation = ?, dietaryPreferences = ? WHERE id = ?`;
  db.query(sql, [name, age, room, status, health, contact, formattedAdmissionDate, medicalRecords, activityParticipation, dietaryPreferences, req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Elderly updated successfully' });
  });
});

// 删除老人
app.delete('/api/elderly/:id', (req, res) => {
  const sql = `DELETE FROM elderly_management WHERE id = ?`;
  db.query(sql, [req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Elderly deleted successfully' });
  });
});
/////////////////////////////////////////////////////////////////////////////////////////////////////////
// 获取所有护工信息
app.get('/api/nurses', (req, res) => {
    const sql = 'SELECT * FROM nurse_management';
    db.query(sql, (err, results) => {
      if (err) throw err;
      res.json(results);
    });
  });
// 添加新护工
app.post('/api/nurses', (req, res) => {
    const { name, age, phone, email, address, emergencyContact, skills, experience, rating, status } = req.body;
    const sql = `INSERT INTO nurse_management (name, age, phone, email, address, emergencyContact, skills, experience, rating, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
    db.query(sql, [name, age, phone, email, address, emergencyContact, skills, experience, rating, status], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Nurse added successfully', id: result.insertId });
    });
  });
  
  // 更新护工信息
  app.put('/api/nurses/:id', (req, res) => {
    const { name, age, phone, email, address, emergencyContact, skills, experience, rating, status } = req.body;
    const sql = `UPDATE nurse_management SET name = ?, age = ?, phone = ?, email = ?, address = ?, emergencyContact = ?, skills = ?, experience = ?, rating = ?, status = ? WHERE id = ?`;
    db.query(sql, [name, age, phone, email, address, emergencyContact, skills, experience, rating, status, req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Nurse updated successfully' });
    });
  });
  
  // 删除护工
  app.delete('/api/nurses/:id', (req, res) => {
    const sql = `DELETE FROM nurse_management WHERE id = ?`;
    db.query(sql, [req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Nurse deleted successfully' });
    });
  });

  // 获取最近活动日志
app.get('/api/recent-activities', (req, res) => {
    const sql = 'SELECT * FROM recent_activities';
    db.query(sql, (err, results) => {
      if (err) throw err;
      res.json(results);
    });
  });
  
  // 添加最近活动日志
  app.post('/api/recent-activities', (req, res) => {
    const { activity } = req.body;
    const sql = `INSERT INTO recent_activities (activity) VALUES (?)`;
    db.query(sql, [activity], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Activity added successfully', id: result.insertId });
    });
  });
  // 删除最近活动日志
app.delete('/api/recent-activities/:id', (req, res) => {
  const sql = `DELETE FROM recent_activities WHERE id = ?`;
  db.query(sql, [req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Activity deleted successfully' });
  });
});

  // 获取待办事项
  app.get('/api/todo-list', (req, res) => {
    const sql = 'SELECT * FROM todo_list';
    db.query(sql, (err, results) => {
      if (err) throw err;
      res.json(results);
    });
  });
  
  // 添加待办事项
  app.post('/api/todo-list', (req, res) => {
    const { text, completed } = req.body;
    const sql = `INSERT INTO todo_list (text, completed) VALUES (?, ?)`;
    db.query(sql, [text, completed], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Todo added successfully', id: result.insertId });
    });
  });
  
  // 更新待办事项
  app.put('/api/todo-list/:id', (req, res) => {
    const { text, completed } = req.body;
    const sql = `UPDATE todo_list SET text = ?, completed = ? WHERE id = ?`;
    db.query(sql, [text, completed, req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Todo updated successfully' });
    });
  });
  
  // 删除待办事项
  app.delete('/api/todo-list/:id', (req, res) => {
    const sql = `DELETE FROM todo_list WHERE id = ?`;
    db.query(sql, [req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Todo deleted successfully' });
    });
  });
/////////////////////////////////////////////////////////////////////////////////////////////////////////
// 获取所有家属信息
app.get('/api/families', (req, res) => {
    const sql = 'SELECT * FROM family_management';
    db.query(sql, (err, results) => {
      if (err) throw err;
      res.json(results);
    });
  });

  // 添加新家属
  app.post('/api/families', (req, res) => {
    const { name, relation, phone, email, address, emergencyContact, elderly } = req.body;
    const sql = `INSERT INTO family_management (name, relation, phone, email, address, emergencyContact, elderly) VALUES (?, ?, ?, ?, ?, ?, ?)`;
    db.query(sql, [name, relation, phone, email, address, emergencyContact, elderly], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Family added successfully', id: result.insertId });
    });
  });
  
  // 更新家属信息
  app.put('/api/families/:id', (req, res) => {
    const { name, relation, phone, email, address, emergencyContact, elderly } = req.body;
    const sql = `UPDATE family_management SET name = ?, relation = ?, phone = ?, email = ?, address = ?, emergencyContact = ?, elderly = ? WHERE id = ?`;
    db.query(sql, [name, relation, phone, email, address, emergencyContact, elderly, req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Family updated successfully' });
    });
  });
  
  // 删除家属
  app.delete('/api/families/:id', (req, res) => {
    const sql = `DELETE FROM family_management WHERE id = ?`;
    db.query(sql, [req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Family deleted successfully' });
    });
  });
  
  // 获取最近活动日志
  app.get('/api/recent-activities-family', (req, res) => {
    const sql = 'SELECT * FROM recent_activities_family_management';
    db.query(sql, (err, results) => {
      if (err) throw err;
      res.json(results);
    });
  });
  
  // 添加最近活动日志
  app.post('/api/recent-activities-family', (req, res) => {
    const { activity } = req.body;
    const sql = `INSERT INTO recent_activities_family_management (activity) VALUES (?)`;
    db.query(sql, [activity], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Activity added successfully', id: result.insertId });
    });
  });
  
  // 更新最近活动日志
  app.put('/api/recent-activities-family/:id', (req, res) => {
    const { activity } = req.body;
    const sql = `UPDATE recent_activities_family_management SET activity = ? WHERE id = ?`;
    db.query(sql, [activity, req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Activity updated successfully' });
    });
  });
  
  // 删除最近活动日志
  app.delete('/api/recent-activities-family/:id', (req, res) => {
    const sql = `DELETE FROM recent_activities_family_management WHERE id = ?`;
    db.query(sql, [req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Activity deleted successfully' });
    });
  });
  
  // 获取待办事项
  app.get('/api/todo-list-family', (req, res) => {
    const sql = 'SELECT * FROM todo_list_family_management';
    db.query(sql, (err, results) => {
      if (err) throw err;
      res.json(results);
    });
  });
  
  // 添加待办事项
  app.post('/api/todo-list-family', (req, res) => {
    const { text, completed } = req.body;
    const sql = `INSERT INTO todo_list_family_management (text, completed) VALUES (?, ?)`;
    db.query(sql, [text, completed], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Todo added successfully', id: result.insertId });
    });
  });
  
  // 更新待办事项
  app.put('/api/todo-list-family/:id', (req, res) => {
    const { text, completed } = req.body;
    const sql = `UPDATE todo_list_family_management SET text = ?, completed = ? WHERE id = ?`;
    db.query(sql, [text, completed, req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Todo updated successfully' });
    });
  });
  
  // 删除待办事项
  app.delete('/api/todo-list-family/:id', (req, res) => {
    const sql = `DELETE FROM todo_list_family_management WHERE id = ?`;
    db.query(sql, [req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Todo deleted successfully' });
    });
  });
/////////////////////////////////////////////////////////////////////////////////////////////////////////
// 获取所有床位信息
app.get('/api/beds', (req, res) => {
    const sql = 'SELECT * FROM bed_management';
    db.query(sql, (err, results) => {
      if (err) throw err;
      res.json(results);
    });
  });
  
  // 添加新床位
  app.post('/api/beds', (req, res) => {
    const { roomNumber, bedNumber, status, elderlyName, admissionDate, careLevel, remarks, operationLog } = req.body;
    
    // 格式化 admissionDate
    const formattedAdmissionDate = new Date(admissionDate).toISOString().split('T')[0];

    const sql = `INSERT INTO bed_management (roomNumber, bedNumber, status, elderlyName, admissionDate, careLevel, remarks, operationLog) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`;
    db.query(sql, [roomNumber, bedNumber, status, elderlyName, formattedAdmissionDate, careLevel, remarks, operationLog], (err, result) => {
        if (err) throw err;
        res.json({ message: 'Bed added successfully', id: result.insertId });
    });
  });
  
  // 更新床位信息
  app.put('/api/beds/:id', (req, res) => {
    const { roomNumber, bedNumber, status, elderlyName, admissionDate, careLevel, remarks, operationLog } = req.body;
    
    // 格式化 admissionDate
    const formattedAdmissionDate = new Date(admissionDate).toISOString().split('T')[0];

    const sql = `UPDATE bed_management SET roomNumber = ?, bedNumber = ?, status = ?, elderlyName = ?, admissionDate = ?, careLevel = ?, remarks = ?, operationLog = ? WHERE id = ?`;
    db.query(sql, [roomNumber, bedNumber, status, elderlyName, formattedAdmissionDate, careLevel, remarks, operationLog, req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Bed updated successfully' });
    });
  });
  
  // 删除床位
  app.delete('/api/beds/:id', (req, res) => {
    const sql = `DELETE FROM bed_management WHERE id = ?`;
    db.query(sql, [req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Bed deleted successfully' });
    });
  });
  
  // 获取所有通知
  app.get('/api/notifications', (req, res) => {
    const sql = 'SELECT * FROM notifications';
    db.query(sql, (err, results) => {
      if (err) throw err;
      res.json(results);
    });
  });
  
  // 添加新通知
  app.post('/api/notifications', (req, res) => {
    const { content } = req.body;
    const sql = `INSERT INTO notifications (content) VALUES (?)`;
    db.query(sql, [content], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Notification added successfully', id: result.insertId });
    });
  });
  
  // 更新通知
  app.put('/api/notifications/:id', (req, res) => {
    const { content } = req.body;
    const sql = `UPDATE notifications SET content = ? WHERE id = ?`;
    db.query(sql, [content, req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Notification updated successfully' });
    });
  });
  
  // 删除通知
  app.delete('/api/notifications/:id', (req, res) => {
    const sql = `DELETE FROM notifications WHERE id = ?`;
    db.query(sql, [req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Notification deleted successfully' });
    });
  });
/////////////////////////////////////////////////////////////////////////////////////////////////////////
// 获取所有合同信息
app.get('/api/contracts', (req, res) => {
    const sql = 'SELECT * FROM admission_contracts';
    db.query(sql, (err, results) => {
      if (err) throw err;
      res.json(results);
    });
  });
  
  // 添加新合同
  app.post('/api/contracts', (req, res) => {
    const { contractNumber, elderlyName, startDate, endDate, status } = req.body;

    // 格式化日期
    const formattedStartDate = new Date(startDate).toISOString().split('T')[0];
    const formattedEndDate = new Date(endDate).toISOString().split('T')[0];

    const sql = `INSERT INTO admission_contracts (contractNumber, elderlyName, startDate, endDate, status) VALUES (?, ?, ?, ?, ?)`;
    db.query(sql, [contractNumber, elderlyName, formattedStartDate, formattedEndDate, status], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Contract added successfully', id: result.insertId });
    });
  });
  
  // 更新合同信息
  app.put('/api/contracts/:id', (req, res) => {
    const { contractNumber, elderlyName, startDate, endDate, status } = req.body;

    // 格式化日期
    const formattedStartDate = new Date(startDate).toISOString().split('T')[0];
    const formattedEndDate = new Date(endDate).toISOString().split('T')[0];

    const sql = `UPDATE admission_contracts SET contractNumber = ?, elderlyName = ?, startDate = ?, endDate = ?, status = ? WHERE id = ?`;
    db.query(sql, [contractNumber, elderlyName, formattedStartDate, formattedEndDate, status, req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Contract updated successfully' });
    });
  });
  
  // 删除合同
  app.delete('/api/contracts/:id', (req, res) => {
    const sql = `DELETE FROM admission_contracts WHERE id = ?`;
    db.query(sql, [req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Contract deleted successfully' });
    });
  });
// 获取所有合同提醒
app.get('/api/contract-reminders', (req, res) => {
    const sql = 'SELECT * FROM contract_reminders';
    db.query(sql, (err, results) => {
      if (err) throw err;
      res.json(results);
    });
  });
  
  // 添加新合同提醒
  app.post('/api/contract-reminders', (req, res) => {
    const { reminder } = req.body;
    const sql = `INSERT INTO contract_reminders (reminder) VALUES (?)`;
    db.query(sql, [reminder], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Reminder added successfully', id: result.insertId });
    });
  });
  
  // 更新合同提醒
  app.put('/api/contract-reminders/:id', (req, res) => {
    const { reminder } = req.body;
    const sql = `UPDATE contract_reminders SET reminder = ? WHERE id = ?`;
    db.query(sql, [reminder, req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Reminder updated successfully' });
    });
  });
  
  // 删除合同提醒
  app.delete('/api/contract-reminders/:id', (req, res) => {
    const sql = `DELETE FROM contract_reminders WHERE id = ?`;
    db.query(sql, [req.params.id], (err, result) => {
      if (err) throw err;
      res.json({ message: 'Reminder deleted successfully' });
    });
  });
/////////////////////////////////////////////////////////////////////////////////////////////////////////
// 获取所有外出登记信息
app.get('/api/outing-registrations', (req, res) => {
  const sql = 'SELECT * FROM outing_registrations';
  db.query(sql, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

// 添加新外出登记
app.post('/api/outing-registrations', (req, res) => {
  const { registrationNumber, elderlyName, outingReason, outingDate, status } = req.body;
  
  // 格式化 outingDate
  const formattedOutingDate = new Date(outingDate).toISOString().split('T')[0];
  
  const sql = `INSERT INTO outing_registrations (registrationNumber, elderlyName, outingReason, outingDate, status) VALUES (?, ?, ?, ?, ?)`;
  db.query(sql, [registrationNumber, elderlyName, outingReason, formattedOutingDate, status], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Registration added successfully', id: result.insertId });
  });
});

// 更新外出登记信息
app.put('/api/outing-registrations/:id', (req, res) => {
  const { registrationNumber, elderlyName, outingReason, outingDate, status } = req.body;
  
  // 格式化 outingDate
  const formattedOutingDate = new Date(outingDate).toISOString().split('T')[0];
  
  // 确保 status 字段不为 NULL
  const updatedStatus = status || '待审批';
  
  const sql = `UPDATE outing_registrations SET registrationNumber = ?, elderlyName = ?, outingReason = ?, outingDate = ?, status = ? WHERE id = ?`;
  db.query(sql, [registrationNumber, elderlyName, outingReason, formattedOutingDate, updatedStatus, req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Registration updated successfully' });
  });
});
// 删除外出登记
app.delete('/api/outing-registrations/:id', (req, res) => {
  const sql = `DELETE FROM outing_registrations WHERE id = ?`;
  db.query(sql, [req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Registration deleted successfully' });
  });
});

// 获取所有外出历史记录
app.get('/api/outing-history', (req, res) => {
  const sql = 'SELECT * FROM outing_history';
  db.query(sql, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

// 添加新外出历史记录
app.post('/api/outing-history', (req, res) => {
  const { history } = req.body;
  const sql = `INSERT INTO outing_history (history) VALUES (?)`;
  db.query(sql, [history], (err, result) => {
    if (err) throw err;
    res.json({ message: 'History added successfully', id: result.insertId });
  });
});

// 更新外出历史记录
app.put('/api/outing-history/:id', (req, res) => {
  const { history } = req.body;
  const sql = `UPDATE outing_history SET history = ? WHERE id = ?`;
  db.query(sql, [history, req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'History updated successfully' });
  });
});

// 删除外出历史记录
app.delete('/api/outing-history/:id', (req, res) => {
  const sql = `DELETE FROM outing_history WHERE id = ?`;
  db.query(sql, [req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'History deleted successfully' });
  });
});

// 获取所有外出安全提示
app.get('/api/safety-tips', (req, res) => {
  const sql = 'SELECT * FROM safety_tips';
  db.query(sql, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

// 添加新外出安全提示
app.post('/api/safety-tips', (req, res) => {
  const { tip } = req.body;
  const sql = `INSERT INTO safety_tips (tip) VALUES (?)`;
  db.query(sql, [tip], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Tip added successfully', id: result.insertId });
  });
});

// 更新外出安全提示
app.put('/api/safety-tips/:id', (req, res) => {
  const { tip } = req.body;
  const sql = `UPDATE safety_tips SET tip = ? WHERE id = ?`;
  db.query(sql, [tip, req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Tip updated successfully' });
  });
});

// 删除外出安全提示
app.delete('/api/safety-tips/:id', (req, res) => {
  const sql = `DELETE FROM safety_tips WHERE id = ?`;
  db.query(sql, [req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Tip deleted successfully' });
  });
});
/////////////////////////////////////////////////////////////////////////////////////////////////////////
// 获取所有来访登记信息
app.get('/api/outing-registrations_vistor', (req, res) => {
  const sql = 'SELECT * FROM outing_registration_vistor';
  db.query(sql, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

// 添加新来访登记
app.post('/api/outing-registrations_vistor', (req, res) => {
  const { registrationNumber, elderlyName, visitDate, visitReason, status } = req.body;
  const formattedVisitDate = new Date(visitDate).toISOString().split('T')[0];
  const sql = `INSERT INTO outing_registration_vistor (registrationNumber, elderlyName, visitDate, visitReason, status) VALUES (?, ?, ?, ?, ?)`;
  db.query(sql, [registrationNumber, elderlyName, formattedVisitDate, visitReason, status], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Registration added successfully', id: result.insertId });
  });
});

// 更新来访登记状态
app.put('/api/outing-registrations_vistor/:id', (req, res) => {
  const { status } = req.body;
  const sql = `UPDATE outing_registration_vistor SET status = ? WHERE id = ?`;
  db.query(sql, [status, req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Registration updated successfully' });
  });
});

// 获取来访历史记录
app.get('/api/outing-history_vistor', (req, res) => {
  const sql = 'SELECT * FROM outing_history_vistor';
  db.query(sql, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

// 添加来访历史记录
app.post('/api/outing-history_vistor', (req, res) => {
  const { date, elderlyName, reason } = req.body;
  const formattedDate = new Date(date).toISOString().split('T')[0];
  const sql = `INSERT INTO outing_history_vistor (date, elderlyName, reason) VALUES (?, ?, ?)`;
  db.query(sql, [formattedDate, elderlyName, reason], (err, result) => {
    if (err) throw err;
    res.json({ message: 'History added successfully', id: result.insertId });
  });
});

// 更新来访历史记录
app.put('/api/outing-history_vistor/:id', (req, res) => {
  const { date, elderlyName, reason } = req.body;
  const formattedDate = new Date(date).toISOString().split('T')[0];
  const sql = `UPDATE outing_history_vistor SET date = ?, elderlyName = ?, reason = ? WHERE id = ?`;
  db.query(sql, [formattedDate, elderlyName, reason, req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'History updated successfully' });
  });
});

// 删除来访历史记录
app.delete('/api/outing-history_vistor/:id', (req, res) => {
  const sql = `DELETE FROM outing_history_vistor WHERE id = ?`;
  db.query(sql, [req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'History deleted successfully' });
  });
});

// 获取来访安全提示
app.get('/api/safety-tips_vistor', (req, res) => {
  const sql = 'SELECT * FROM safety_tips_vistor';
  db.query(sql, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

// 添加来访安全提示
app.post('/api/safety-tips_vistor', (req, res) => {
  const { tip } = req.body;
  const sql = `INSERT INTO safety_tips_vistor (tip) VALUES (?)`;
  db.query(sql, [tip], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Tip added successfully', id: result.insertId });
  });
});

// 更新来访安全提示
app.put('/api/safety-tips_vistor/:id', (req, res) => {
  const { tip } = req.body;
  const sql = `UPDATE safety_tips_vistor SET tip = ? WHERE id = ?`;
  db.query(sql, [tip, req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Tip updated successfully' });
  });
});

// 删除来访安全提示
app.delete('/api/safety-tips_vistor/:id', (req, res) => {
  const sql = `DELETE FROM safety_tips_vistor WHERE id = ?`;
  db.query(sql, [req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Tip deleted successfully' });
  });
});
/////////////////////////////////////////////////////////////////////////////////////////////////////////
// 获取所有事故记录
app.get('/api/incident-records', (req, res) => {
  const sql = 'SELECT * FROM incident_records';
  db.query(sql, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

// 添加新事故记录
app.post('/api/incident-records', (req, res) => {
  const { incidentType, description, date } = req.body;
  
  // 格式化日期
  const formattedDate = new Date(date).toISOString().split('T')[0];


  const sql = `INSERT INTO incident_records (incidentType, description, date) VALUES (?, ?, ?)`;
  db.query(sql, [incidentType, description, formattedDate], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Incident record added successfully', id: result.insertId });
  });
});

// 更新事故记录
app.put('/api/incident-records/:id', (req, res) => {
  const { incidentType, description, date } = req.body;
  
  // 格式化日期
  const formattedDate = new Date(date).toISOString().split('T')[0];

  const sql = `UPDATE incident_records SET incidentType = ?, description = ?, date = ? WHERE id = ?`;
  db.query(sql, [incidentType, description, formattedDate, req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Incident record updated successfully' });
  });
});

// 删除事故记录
app.delete('/api/incident-records/:id', (req, res) => {
  const sql = `DELETE FROM incident_records WHERE id = ?`;
  db.query(sql, [req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Incident record deleted successfully' });
  });
});

///////////////////////////////////////////////////////////////////////////////////////////////////////// 
// 获取所有退住申请记录
app.get('/api/discharge-applications', (req, res) => {
  const sql = 'SELECT * FROM discharge_applications';
  db.query(sql, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

// 添加新退住申请记录
app.post('/api/discharge-applications', (req, res) => {
  const { residentName, reason, date } = req.body;
  
  // 格式化日期
  const formattedDate = new Date(date).toISOString().split('T')[0];

  const sql = `INSERT INTO discharge_applications (residentName, reason, date) VALUES (?, ?, ?)`;
  db.query(sql, [residentName, reason, formattedDate], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Discharge application added successfully', id: result.insertId });
  });
});

// 更新退住申请记录
app.put('/api/discharge-applications/:id', (req, res) => {
  const { residentName, reason, date } = req.body;
  
  // 格式化日期
  const formattedDate = new Date(date).toISOString().split('T')[0];

  const sql = `UPDATE discharge_applications SET residentName = ?, reason = ?, date = ? WHERE id = ?`;
  db.query(sql, [residentName, reason, formattedDate, req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Discharge application updated successfully' });
  });
});

// 删除退住申请记录
app.delete('/api/discharge-applications/:id', (req, res) => {
  const sql = `DELETE FROM discharge_applications WHERE id = ?`;
  db.query(sql, [req.params.id], (err, result) => {
    if (err) throw err;
    res.json({ message: 'Discharge application deleted successfully' });
  });
});
app.listen(port, () => {
  console.log(`Server running on port ${port}`);
});

/////////////////////////////////////////////////////////////////////////////////////////////////////////
// 获取概览数据
app.get('/api/overview', (req, res) => {
  const queries = [
    'SELECT COUNT(*) AS elderlyCount FROM elderly_management WHERE status = "在住"',
    'SELECT COUNT(*) AS nurseCount FROM nurse_management WHERE status = "在岗"',
    'SELECT COUNT(*) AS taskCount FROM todo_list WHERE completed = 0',
    'SELECT COUNT(*) AS totalBeds FROM bed_management',
    'SELECT COUNT(*) AS occupiedBeds FROM bed_management WHERE status = "已入住"'
  ];

  let results = [];
  let index = 0;

  function executeNextQuery() {
    if (index < queries.length) {
      db.query(queries[index], (err, result) => {
        if (err) throw err;
        results.push(result[0]);
        index++;
        executeNextQuery();
      });
    } else {
      const totalBeds = results[3].totalBeds;
      const occupiedBeds = results[4].occupiedBeds;
      const roomUsage = totalBeds > 0 ? (occupiedBeds / totalBeds * 100).toFixed(2) : 0;
      res.json({
        elderlyCount: results[0].elderlyCount,
        nurseCount: results[1].nurseCount,
        taskCount: results[2].taskCount,
        roomUsage: roomUsage + '%'
      });
    }
  }
  executeNextQuery();
});
// 获取业绩排行数据
app.get('/api/performance', (req, res) => {
  const sql = `
    SELECT nm.name, p.performance
    FROM performance p
    JOIN nurse_management nm ON p.nurse_id = nm.id
    ORDER BY p.performance DESC
    LIMIT 10;
  `;
  db.query(sql, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});
// 获取出勤率数据
app.get('/api/attendance', (req, res) => {
  const sql = `
    SELECT nm.name, a.attendance
    FROM attendance a
    JOIN nurse_management nm ON a.nurse_id = nm.id
    ORDER BY a.attendance DESC
    LIMIT 10;
  `;
  db.query(sql, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});
// 获取业务趋势数据
app.get('/api/business-trend', (req, res) => {
  const sql = `
    SELECT month, SUM(business_volume) AS businessVolume
    FROM business_trend
    GROUP BY month
    ORDER BY month ASC;
  `;
  db.query(sql, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});
// 获取任务完成数和任务总数
app.get('/api/task-completion', (req, res) => {
  const { startDate, endDate } = req.query;

  const sql = `
  SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS date, COUNT(*) AS totalTasks, SUM(completed) AS completedTasks
  FROM todo_list
  WHERE created_at BETWEEN '2024-12-01' AND '2024-12-31'
  GROUP BY date
  ORDER BY date ASC;
  `;

  db.query(sql, [startDate, endDate], (err, results) => {
    if (err) throw err;
    
    const taskCompletionData = results.map(item => ({
      date: item.date,
      totalTasks: item.totalTasks,
      completedTasks: item.completedTasks,
      completionRate: ((item.completedTasks / item.totalTasks) * 100).toFixed(2)
    }));

    res.json(taskCompletionData);
  });
});
// 获取房间使用率数据
app.get('/api/room-usage', (req, res) => {
  const sql = `
    SELECT DATE_FORMAT(admissionDate, '%Y-%m') AS month, COUNT(*) AS totalBeds, SUM(status = '已入住') AS occupiedBeds
    FROM bed_management
    GROUP BY month
    ORDER BY month ASC;
  `;
  db.query(sql, (err, results) => {
    if (err) throw err;
    const usageRateData = results.map(item => ({
      month: item.month,
      totalBeds: item.totalBeds,
      occupiedBeds: item.occupiedBeds,
      usageRate: (item.occupiedBeds / item.totalBeds * 100).toFixed(2)
    }));
    res.json(usageRateData);
  });
});